﻿Imports System.Data.SqlClient

Public Class frmRKilometros
    Dim OdometroInicial(2000) As Integer
    Dim KilometrosAcumulados(2000) As Integer
    Dim KilometrosOpto(2000) As Integer
    Dim KilometrosDiesel(2000) As Integer

    Private Sub frmRKilometros_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        txtAnio.Text = Now.Year
    End Sub
    Sub CrearReporte()
        Dim strMes As String = lstMes.Text
        Dim Mes As Integer = 0
        Dim anio As Integer = txtAnio.Text

        Select Case strMes
            Case "Enero"
                Mes = 1
            Case "Febrero"
                Mes = 2
            Case "Marzo"
                Mes = 3
            Case "Abril"
                Mes = 4
            Case "Mayo"
                Mes = 5
            Case "Junio"
                Mes = 6
            Case "Julio"
                Mes = 7
            Case "Agosto"
                Mes = 8
            Case "Septiembre"
                Mes = 9
            Case "Octubre"
                Mes = 10
            Case "Noviembre"
                Mes = 11
            Case "Diciembre"
                Mes = 12
        End Select

        Dim IDKms As Integer = BuscarIntDatoCitra("SELECT COUNT(*) FROM Kilometros WHERE Ejercicio=" & anio & " AND Periodo=" & Mes)

        If IDKms > 0 Then
            'SACA LOS KILOMETROS DE LA TABLA AUTOBUSES IMPORTE03
            CalculadeAutobuses(anio, Mes)
            KilometrosRegistrados2()
            KilometrosOptocontrol(anio, Mes)
            Diesel(Mes, anio)
            CreaReporte(Mes, anio)
        Else
            'Calcula los kms, de las bases de datos de opto y de kms
            'PRIMERO TRAE EL ODOMETRO INICIAL
            'LE SUMA LOS KILOMETROS QUE SE TENGAN REGISTRADOS EN LA TABLA KILOMETROS
            'SE LE SUMAN LOS KILOMETROS DEL MES REGISTRADOS EN LA TABLA REGISTRO DE OPTOCONTROL
            'SE COMPLEMENTA CON LOS KILOMETROS REGISTRADOS EN DIESEL
            TraeOdometroInicial()
            KilometrosRegistrados()
            KilometrosOptocontrol(anio, Mes)
            Diesel(Mes, anio)
            CreaReporte(Mes, anio)
        End If
    End Sub
    Sub CreaReporte(ByVal Mes As Integer, ByVal Anio As Integer)
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = "SELECT IDAUTOBUS,CCODIGOC01,NOECONOMICO FROM AUTOBUSES WHERE ESTATUS=1 ORDER BY NOECONOMICO"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        Dim totalUB As Double = 0
        Dim TotalL As Double = 0
        Dim totalV As Double = 0

        Dim totalUBG As Double = 0
        Dim TotalLG As Double = 0
        Dim totalVG As Double = 0

        wb.SHEETS(1).cells(rng, 2).value = "No. Interno"
        wb.SHEETS(1).cells(rng, 3).value = "No. Económico"
        wb.SHEETS(1).cells(rng, 4).value = "Odometro"
        wb.SHEETS(1).cells(rng, 5).value = "Kms. del mes"
        wb.SHEETS(1).cells(rng, 6).value = "Costo 0 a 175,000"
        wb.SHEETS(1).cells(rng, 7).value = "Costo 175,001 a 350,000"
        wb.SHEETS(1).cells(rng, 8).value = "Costo 350,001 a 525,000"
        wb.SHEETS(1).cells(rng, 9).value = "Costo 525,001 a 700,000"
        wb.SHEETS(1).cells(rng, 10).value = "Costo 700,001 a 875,000"
        wb.SHEETS(1).cells(rng, 11).value = "Costo 875,001 a 1,050,000"
        wb.SHEETS(1).cells(rng, 12).value = "Total"

        For i = 2 To 12
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).CELLS(rng, i).HorizontalAlignment = -4108
            wb.SHEETS(1).CELLS(rng, i).VerticalAlignment = -4108
            wb.SHEETS(1).CELLS(rng, i).WrapText = True
            wb.SHEETS(1).CELLS(rng, i).Orientation = 0
            wb.SHEETS(1).CELLS(rng, i).AddIndent = False
            wb.SHEETS(1).CELLS(rng, i).IndentLevel = 0
            wb.SHEETS(1).CELLS(rng, i).ShrinkToFit = False
            wb.SHEETS(1).CELLS(rng, i).MergeCells = False
            wb.SHEETS(1).CELLS(rng, i).ReadingOrder = -5002
        Next
       

        wb.SHEETS(1).columns(2).columnwidth = 12
        wb.SHEETS(1).columns(3).columnwidth = 12
        wb.SHEETS(1).columns(4).columnwidth = 15
        wb.SHEETS(1).columns(5).columnwidth = 15

        For i = 4 To 5
            wb.SHEETS(1).columns(i).NumberFormat = "#,##0"
        Next

        For i = 6 To 13
            wb.SHEETS(1).columns(i).NumberFormat = "#,##0.00"
        Next

        rng = rng + 1

        Dim IdAutobus As Integer = 0
      
        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("CCODIGOC01")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("NOECONOMICO")
            wb.SHEETS(1).cells(rng, 4).value = OdometroInicial(IdAutobus) + KilometrosAcumulados(IdAutobus) + KilometrosOpto(IdAutobus) + KilometrosDiesel(IdAutobus)
            wb.SHEETS(1).cells(rng, 5).value = KilometrosOpto(IdAutobus) + KilometrosDiesel(IdAutobus)


            rng = rng + 1
        Loop

        Dim Kms As Integer = 0
        Dim NoInterno As String = ""
        Dim KmsDiesel As Double = 0

        For i = 6 To rng - 1
            Kms = wb.SHEETS(1).cells(i, 5).value
            If Kms = 0 Then
                NoInterno = wb.SHEETS(1).cells(i, 2).value
                IdAutobus = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE cCodigoC01='" & NoInterno.Trim & "'")
                KmsDiesel = BuscarDblDatoCitra("SELECT SUM(KilometrosTotales) as KMS FROM Diesel WHERE IdAutobus=" & IdAutobus & " AND MONTH(Fecha)=" & Mes & " AND YEAR(Fecha)=" & Anio)
                wb.SHEETS(1).cells(i, 5).value = KmsDiesel
            End If
        Next

        Dim OdoBus As Double = 0
        Dim KmsBus As Double = 0
        Dim OdoIni As Double = 0
        Dim CostoKms As Double = 0
        Dim OdoAnterior As Double = 0
        Dim OdoPosterior As Double = 0

        For i = 6 To rng - 1
            OdoBus = wb.SHEETS(1).cells(i, 4).value
            KmsBus = wb.SHEETS(1).cells(i, 5).value
            OdoIni = OdoBus - KmsBus
            


            If OdoBus < 175001 Then
                CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=1") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=1")
                wb.SHEETS(1).cells(i, 6).value = KmsBus * CostoKms
            ElseIf OdoBus > 175000 And OdoBus < 350001 Then
                If OdoIni > 175000 Then
                    'EL ODOMETRO INICIAL ESTA DENTRO DEL RANGO ACTUAL
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=2") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=2")
                    wb.SHEETS(1).cells(i, 7).value = KmsBus * CostoKms
                Else
                    'EL ODOMETRO INICIAL ESTA DENTRO DEL RANGO ANTERIOR
                    OdoPosterior = OdoBus - 175000
                    OdoAnterior = KmsBus - OdoPosterior
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=1") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=1")
                    wb.SHEETS(1).cells(i, 6).value = OdoAnterior * CostoKms
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=2") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=2")
                    wb.SHEETS(1).cells(i, 7).value = OdoPosterior * CostoKms
                End If
            ElseIf OdoBus > 350000 And OdoBus < 525001 Then
                If OdoIni > 350000 Then
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=3") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=3")
                    wb.SHEETS(1).cells(i, 8).value = KmsBus * CostoKms
                Else
                    OdoPosterior = OdoBus - 350000
                    OdoAnterior = KmsBus - OdoPosterior
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=2") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=2")
                    wb.SHEETS(1).cells(i, 7).value = OdoAnterior * CostoKms
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=3") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=3")
                    wb.SHEETS(1).cells(i, 8).value = OdoPosterior * CostoKms
                End If
            ElseIf OdoBus > 525000 And OdoBus < 700001 Then
                If OdoIni > 525000 Then
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=4") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=4")
                    wb.SHEETS(1).cells(i, 9).value = KmsBus * CostoKms
                Else
                    OdoPosterior = OdoBus - 525000
                    OdoAnterior = KmsBus - OdoPosterior
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=3") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=3")
                    wb.SHEETS(1).cells(i, 8).value = OdoAnterior * CostoKms
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=4") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=4")
                    wb.SHEETS(1).cells(i, 9).value = OdoPosterior * CostoKms
                End If
            ElseIf OdoBus > 700000 And OdoBus < 875001 Then
                If OdoIni > 700000 Then
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=5") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=5")
                    wb.SHEETS(1).cells(i, 10).value = KmsBus * CostoKms
                Else
                    OdoPosterior = OdoBus - 700000
                    OdoAnterior = KmsBus - OdoPosterior
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=4") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=4")
                    wb.SHEETS(1).cells(i, 9).value = OdoAnterior * CostoKms
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=5") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=5")
                    wb.SHEETS(1).cells(i, 10).value = OdoPosterior * CostoKms
                End If
            ElseIf OdoBus > 875000 And OdoBus < 1050001 Then
                If OdoIni > 875000 Then
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=6") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=6")
                    wb.SHEETS(1).cells(i, 11).value = KmsBus * CostoKms
                Else
                    OdoPosterior = OdoBus - 875000
                    OdoAnterior = KmsBus - OdoPosterior
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=5") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=5")
                    wb.SHEETS(1).cells(i, 10).value = OdoAnterior * CostoKms
                    CostoKms = BuscarDblDatoCitra("SELECT FactorScannia FROM RangoKms WHERE IdRango=6") + BuscarDblDatoCitra("SELECT FactorLlantas FROM RangoKms WHERE IdRango=6")
                    wb.SHEETS(1).cells(i, 11).value = OdoPosterior * CostoKms
                End If
            End If
            wb.SHEETS(1).cells(i, 12).value = "=SUMA(F" & i & ":K" & i & ")"

        Next
        wb.SHEETS(1).cells(i, 6).value = "=SUMA(F6:F" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 7).value = "=SUMA(G6:G" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 8).value = "=SUMA(H6:H" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 9).value = "=SUMA(I6:I" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 10).value = "=SUMA(J6:J" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 11).value = "=SUMA(K6:K" & i - 1 & ")"
        wb.SHEETS(1).cells(i, 12).value = "=SUMA(L6:L" & i - 1 & ")"

        wb.SHEETS(1).cells(1, 2).value = "Consorcio Optra"
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Kilometros del mes de " & lstMes.Text & " de " & txtAnio.Text
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696


    End Sub
    Sub Diesel(ByVal Mes As Integer, ByVal Anio As Integer)
        Dim cnConn As New SqlConnection
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader
        Dim strSql As String

        cnConn.ConnectionString = CitraConnection
        strSql = "SELECT D.IDAUTOBUS,A.NOECONOMICO,SUM(D.KILOMETROSTOTALES) AS KMS FROM DIESEL D INNER JOIN AUTOBUSES A ON D.IDAUTOBUS=A.IDAUTOBUS WHERE A.ESTATUS=1 AND A.TEXTO02='' AND YEAR(FECHA)=" & Anio & " AND MONTH(FECHA)=" & Mes & " GROUP BY D.IDAUTOBUS,A.NOECONOMICO"

        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandTimeout = 0
        cmdBuscar.CommandText = strSql

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdAutobus As Integer = 0

        Do While rdBuscar.Read
            IdAutobus = rdBuscar("IDAUTOBUS")
            KilometrosDiesel(IdAutobus) = rdBuscar("KMS")
        Loop
    End Sub
    Sub KilometrosOptocontrol(ByVal Anio As Integer, ByVal Mes As Integer)
        Dim cnConn As New SqlConnection
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader
        Dim strSql As String

        cnConn.ConnectionString = OptocontrolConnection
        strSql = "SELECT ID_VEHICULO,SUM(DISTANCIA) AS KMS FROM REGISTRO WHERE YEAR(FECHA)=" & Anio & " AND MONTH(FECHA)=" & Mes & " GROUP BY ID_VEHICULO"
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandTimeout = 0
        cmdBuscar.CommandText = strSql

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        ' Dim g As Guid
        Dim IdAutobus As Integer = 0
        Dim IdOpto As String = ""
        Dim ID_VEHICULO As String = ""


        Do While rdBuscar.Read
            'g = rdBuscar("ID_VEHICULO")
            ID_VEHICULO = rdBuscar("ID_VEHICULO")
            
            IdOpto = BuscarStrDatoCitra("SELECT Texto02 FROM Autobuses WHERE UPPER(Texto02)='" & UCase(ID_VEHICULO) & "'")
            If Len(IdOpto.Trim) > 0 Then
                'SI TIENE ID DE OPTOCONTROL GUARDA LOS DATOS EN LA TABLA KILOMETROS
                IdAutobus = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE UPPER(Texto02)='" & UCase(ID_VEHICULO) & "'")
                KilometrosOpto(IdAutobus) = rdBuscar("KMS")
            End If
        Loop
    End Sub
    Sub TraeOdometroInicial()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = "SELECT IDAUTOBUS,IMPORTE04 FROM AUTOBUSES WHERE Estatus=1"


        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdAutobus As Integer = 0

        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
            OdometroInicial(IdAutobus) = rdBuscar("Importe04")
        Loop
    End Sub
    Sub KilometrosRegistrados()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = "SELECT IDAUTOBUS,SUM(KMS) AS KMS FROM KILOMETROS GROUP BY IDAUTOBUS"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdAutobus As Integer = 0

        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
            KilometrosAcumulados(IdAutobus) = rdBuscar("KMS")
        Loop
    End Sub
    Sub KilometrosRegistrados2()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = "SELECT IDAUTOBUS,SUM(KMS) AS KMS FROM KILOMETROS GROUP BY IDAUTOBUS"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdAutobus As Integer = 0

        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
            KilometrosAcumulados(IdAutobus) = 0
        Loop
    End Sub
    Sub CalculadeAutobuses(ByVal Ejercicio As Integer, ByVal Periodo As Integer)
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = "SELECT CCODIGOC01,NOECONOMICO,ODOMETRO,KMS FROM KILOMETROS K INNER JOIN AUTOBUSES A ON K.IDAUTOBUS=A.IDAUTOBUS WHERE Ejercicio=" & Ejercicio & " AND Periodo=" & Periodo & " ORDER BY NOECONOMICO"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdAutobus As Integer

        Do While rdBuscar.Read()
            IdAutobus = rdBuscar("IdAutobus")
            OdometroInicial(IdAutobus) = rdBuscar("Odometro")
        Loop
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()

    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        CrearReporte()

    End Sub
End Class